package com.iris.live.services.data.repositories.impl;

import com.iris.live.services.common.*;
import com.iris.live.services.common.reconsitution.BIExtractColumnMetaData;
import com.iris.live.services.common.reconsitution.BIExtractMetaData;
import com.iris.live.services.data.repositories.BIExtractRepository;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import javax.persistence.Query;
import java.text.DecimalFormat;
import java.util.*;

/**
 * @Description:BI 数据抽取接口时间
 * @author: Tony.Zhang
 * @Date: 2017-09-12 14:56
 **/
@Repository
@Transactional
public class BIExtractRepositoryImpl extends BaseRepository implements BIExtractRepository {
    private static final Logger logger = LoggerFactory.getLogger(BIExtractRepositoryImpl.class);

    public BIExtractRepositoryImpl() {

    }

    @Override
    public String extractDailyData(String date) {
        return this.extractDailyData(date, null);
    }

    @Override
    public String extractDailyData(String date, String name) {
        String  tableName = String.format("t_bi_daily_stat_data_%s", date.substring(0, 4));

        try {
            Date now = new Date();
            if (Integer.valueOf(date.replace("-", "")) > Integer.valueOf(DateFormatUtils.getFormatDateToString(now, "yyyyMMdd"))) {
                return "success";
            }

            getRetailedVehicle(date, date);

            long startTime = System.currentTimeMillis();
            logger.warn("开始同步日数据-{}, 日期={}", tableName, date);
            createKPIData(name, date, date, date, tableName);
            logger.warn("结束同步日数据-{}, 日期={}, 用时={}", tableName, date, (System.currentTimeMillis() - startTime) / 1000);
        }catch(Exception exc) {
            logger.error("同步日数据。 .", exc);
            return "failure";
        }
        return "success";
    }

    @Override
    public String extractMonthlyData(String date) {
        return this.extractMonthlyData(date, null);
    }

    @Override
    public String extractMonthlyData(String date, String name) {
        String  year = date.substring(0,4), month = date.substring(5,7),
                fromDay = DateFormatUtils.getFirstDayOfMonth(year, month),
                endDay =  DateFormatUtils.getEndDayOfMonth(year, month),
                tableName = "t_bi_monthly_stat_data";
        Date now = new Date();

        try {
            if(Integer.valueOf(year + month) > Integer.valueOf(DateFormatUtils.getFormatDateToString(now, "yyyyMM"))){
                return "success";
            }

            //当前月的结束时间取系统当前时间
            if (Integer.valueOf(year + month) == Integer.valueOf(DateFormatUtils.getFormatDateToString(now, "yyyyMM"))) {
                endDay = DateFormatUtils.getFormatDateToString(now, DateFormatUtils.TIME_FORMAT_STR_YYYY_MM_DD);
            }

            getRetailedVehicle(fromDay, endDay);

            long startTime = System.currentTimeMillis();
            logger.warn("开始同步日数据-{}， 月份={}", tableName, month);
            createKPIData(name, fromDay, endDay, year + month, tableName);
            logger.warn("结束同步日数据-{}， 月份={}, 用时={} ", tableName,month, (System.currentTimeMillis() - startTime)/1000);

        }catch(Exception exc){
            logger.error("同步月数据.", exc);
            return "failure";
        }
        return "success";
    }

    /**
     * 生成中间表数据
     * @param fromDay
     * @param endDay
     * @param date
     * @param tableName
     */
    private void createKPIData(String statementCode, String fromDay, String endDay, String date, String tableName){
        BIExtractStatements statements = BIExtractStatements.getInstance();
        List<BIExtractMetaData> metas = statements.getMetaDatas();

        int index;
        String  replaceStatement = null,selectStatement, name;
        StringBuffer batch = new StringBuffer(), updateStr = new StringBuffer();

        //生成月数据
        List<BIExtractColumnMetaData> columnMetaDatas = null;
        Map params = new HashMap();
        params.put("stat_date", date);
        for(BIExtractMetaData meta : metas){
            if(statementCode != null && !statementCode.equals(meta.getCode())){
                continue;
            }
            columnMetaDatas = meta.getExtractColumnMetaDatas();

            //清除历史数据
            logger.warn("清除历史数据, 表={}, 统计日期={}", tableName, date);
            updateStr.setLength(0);
            for(index = 0; index < columnMetaDatas.size(); index++){
                if(index == 0){
                    updateStr.append(" UPDATE ").append(tableName).append(" set ");
                }else{
                    updateStr.append(", ");
                }
                updateStr.append(columnMetaDatas.get(index).getBiName()).append(" = 0 ");
            }
            updateStr.append(" WHERE STAT_DATE =:stat_date");
            if(logger.isInfoEnabled()) {
                logger.warn(updateStr.toString());
            }
            super.executeUpdate(updateStr.toString(), params);

            name = meta.getName();
            selectStatement = this.getSelectStatement(meta, fromDay, endDay);
            List<Map> resultSet = this.getList(selectStatement);

            //清除历史数据
            logger.warn("清除历史数据, 表={}, 统计日期={}", tableName, date);
            updateStr.setLength(0);
            for(index = 0; index < columnMetaDatas.size(); index++){
                if(index == 0){
                    updateStr.append(" UPDATE ").append(tableName).append(" set ");
                }else{
                    updateStr.append(", ");
                }
                updateStr.append(columnMetaDatas.get(index).getBiName()).append(" = 0 ");
            }
            updateStr.append(" WHERE STAT_DATE =:stat_date");
            if(logger.isInfoEnabled()) {
                logger.warn(updateStr.toString());
            }
            super.executeUpdate(updateStr.toString(), params);

            index = 1;
            batch.setLength(0);
            for(Map rs : resultSet){
                if(index % 1000 == 0){
                    replaceStatement = this.getReplaceStatement(meta, tableName, batch.toString());
                    batchImport(name,replaceStatement);

                    index = 1;
                    batch.setLength(0);
                }

                //集团, 大区_EN, 小区_EN, CTA_EN, 许可_BMBS,'").append(date).append("',来源,品牌,级别"
                if(batch.length() > 0){
                    batch.append(",");
                }

                batch.append("(");
                batch.append("'").append(rs.get("集团")).append("',");
                batch.append("'").append(rs.get("大区_EN")).append("',");
                batch.append("'").append(rs.get("大区")).append("',");
                batch.append("'").append(rs.get("小区_EN")).append("',");
                batch.append("'").append(rs.get("小区")).append("',");
                batch.append("'").append(rs.get("CTA_EN")).append("',");
                batch.append("'").append(rs.get("CTA")).append("',");
                batch.append("'").append(rs.get("许可_BMBS")).append("',");
                batch.append("'").append(rs.get("经销商名")).append("',");
                batch.append("'").append(rs.get("经销商名_EN")).append("',");
                batch.append("'").append(rs.get("salesCode")).append("',");

                batch.append("'").append(date).append("',");
                batch.append("'").append(rs.get("来源")).append("',");
                batch.append("'").append(rs.get("品牌")).append("',");
                batch.append("'").append(rs.get("级别")).append("'");

                for(BIExtractColumnMetaData columnMetaData : columnMetaDatas){
                    batch.append(",").append(rs.get(columnMetaData.getBiName()));
                }

                batch.append(")");

                index += 1;
            }

            if(batch.length() > 0) {
                replaceStatement = this.getReplaceStatement(meta, tableName, batch.toString());
                batchImport(name, replaceStatement);
            }
        }
    }

    /**
     *
     * @param name
     * @param replaceStatement
     */
    private void batchImport(String name, String replaceStatement){
        try{
            Query update = this.entities.createNativeQuery(replaceStatement);

            update = this.entities.createNativeQuery(replaceStatement);
            int updatedNum = update.executeUpdate();
            if (updatedNum <= 0) {
                logger.warn("Update failed.");
            }
        }catch(Exception exc){
            logger.error("name=" + name, exc);

            throw exc;
        }
    }

    /**
     * 获取数据库数据批量更新语句
     * @param meta
     * @param tableName
     * @param values
     * @return
     */
    private String getReplaceStatement(BIExtractMetaData meta, String tableName, String values) {
        StringBuffer replaceStatement = new StringBuffer("INSERT INTO ").append(tableName).append(" (group_code, b_region_code, b_region, s_region_code, s_region, pma_code, pma, permit, permit_cn,permit_en,sales_code, stat_date, vehicle_source, vehicle_brand, vehicle_level");

        List<BIExtractColumnMetaData> columnMetaDatas = meta.getExtractColumnMetaDatas();
        for(BIExtractColumnMetaData columnMetaData : columnMetaDatas){
            replaceStatement.append(",").append(columnMetaData.getBiName());
        }

        replaceStatement.append(") values ");
        replaceStatement.append(values);
        replaceStatement.append(" ON DUPLICATE KEY UPDATE ");
        replaceStatement.append(" group_code=values(group_code),");
        replaceStatement.append(" b_region_code=values(b_region_code),");
        replaceStatement.append(" b_region=values(b_region),");
        replaceStatement.append(" s_region_code=values(s_region_code),");
        replaceStatement.append(" s_region=values(s_region),");
        replaceStatement.append(" pma_code=values(pma_code),");
        replaceStatement.append(" pma=values(pma),");
        replaceStatement.append(" permit=values(permit),");
        replaceStatement.append(" permit_cn=values(permit_cn),");
        replaceStatement.append(" permit_en=values(permit_en),");
        replaceStatement.append(" sales_code=values(sales_code),");
        replaceStatement.append(" stat_date=values(stat_date),");
        replaceStatement.append(" vehicle_source=values(vehicle_source),");
        replaceStatement.append(" vehicle_brand=values(vehicle_brand),");
        replaceStatement.append(" vehicle_level=values(vehicle_level)");

        for(BIExtractColumnMetaData columnMetaData : columnMetaDatas){
            replaceStatement.append(",").append(columnMetaData.getBiName()).append("=values(").append(columnMetaData.getBiName()).append(")");
        }

        return replaceStatement.toString();
    }

    /**
     * 构建获取原始数据的脚本
     * @param meta
     * @param startDate
     * @param endDate
     * @return
     */
    private String getSelectStatement(BIExtractMetaData meta, String startDate, String endDate) {
        String date = null;
        if(startDate.equals(endDate)){
            date = startDate;
        }else{
            date = startDate.substring(5,7);
        }

        StringBuffer selectStatement = new StringBuffer();
        selectStatement.append(" select 集团, 大区_EN, 大区, 小区_EN, 小区, CTA_EN, CTA, 许可_BMBS, 经销商名, 经销商名_EN, salesCode , '").append(date).append("' 统计日期 ,来源,品牌,级别");

        List<BIExtractColumnMetaData> columnMetaDatas = meta.getExtractColumnMetaDatas();
        for(BIExtractColumnMetaData columnMetaData : columnMetaDatas) {
            selectStatement.append(", IFNULL(SUM(").append(columnMetaData.getName()).append("),0) ");
            if (columnMetaData.getExtName() != null) {
                selectStatement.append(" + IFNULL(SUM(").append(columnMetaData.getExtName()).append(")/count(1),0) ");
            }

            selectStatement.append(columnMetaData.getBiName());
        }

        selectStatement.append(" from (").append(meta.getSql()).append(") a ")
                .append(" GROUP BY 许可_BMBS, 来源,品牌, 级别");

        return selectStatement.toString().replace("'MB'", "''").replace("2020-01-01", endDate).replace("2000-01-01", startDate);
    }

    private void getRetailedVehicle(String startDate, String endDate) throws Exception{
        String sql = " SELECT  初始报价单号, 报价编号, 类型 , 处理时间, 换票, 退票 FROM ( " +
                     "      SELECT `初始报价单号`, `报价编号`, 处理时间,  类型 , 换票, 退票 FROM ( " +
                     "          SELECT quote.`初始报价单号`, quote.`报价编号`, 开票时间 处理时间,  1 类型 , IF(换票时间 IS NULL, 0, 1) 换票, IF(退票时间 IS NULL, 0, 1) 退票 " +
                     "          FROM  报价单表 quote, 报价交车表 retail " +
                     "          WHERE quote.`交车` = retail.`编号` " +
                     "          AND quote.`是否还原点` = '是' " +
                     "          AND quote.`是否驳回` = '否' " +
                     "          AND retail.`开票时间` >= CONCAT('2000-01-01',' 00:00:01') " +
                     "          AND retail.`开票时间` <= CONCAT('2020-01-01',' 23:59:59') " +
                     "          ORDER BY quote.`初始报价单号` ASC, 开票时间 DESC, quote.`报价编号` DESC " +
                     "      ) A GROUP BY `初始报价单号`, 处理时间 " +
                     "  UNION ALL " +
                     "      SELECT `初始报价单号`, `报价编号`, 处理时间,  类型 , 换票, 退票 FROM ( " +
                     "          SELECT quote.`初始报价单号`, quote.`报价编号`, 退票时间 处理时间,  -1 类型 , 0 换票, 1 退票 " +
                     "          FROM  报价单表 quote, 报价交车表 retail " +
                     "          WHERE quote.`交车` = retail.`编号` " +
                     "          AND quote.`是否还原点` = '是' " +
                     "          AND quote.`是否驳回` = '否' " +
                     "          AND retail.`退票时间` >= CONCAT('2000-01-01',' 00:00:01') " +
                     "          AND retail.`退票时间` <= CONCAT('2020-01-01',' 23:59:59') " +
                     "          ORDER BY quote.`初始报价单号` ASC, 退票时间 DESC, quote.`报价编号` DESC " +
                     "      ) A GROUP BY `初始报价单号`, 处理时间 " +
                     " )  a " +
                     " ORDER BY 初始报价单号, 处理时间 asc, 类型 asc ";

        String delStr = "TRUNCATE TABLE T_RETAILED_INVOICE_LOG ";

        Map params = new HashMap();
        super.executeUpdate(delStr, params);

        sql = sql.replaceAll("2020-01-01",endDate).replaceAll("2000-01-01", startDate);
        List<Map> result = this.getList(sql, params);
        String initNo = "", type = "";

        String insert = "INSERT INTO T_RETAILED_INVOICE_LOG(init_No,quote_id, process_time, type)  values ";
        StringBuffer values = new StringBuffer();
        if(result != null){
            Map item = null;
            for(int index = 0; index < result.size(); index++){
                item = result.get(index);
                //同一初始报价单号范围内，相邻两条是开票，同时开票时间不等而退票时间或者换票时间为空，忽略
                if(item.get("初始报价单号").toString().equalsIgnoreCase(initNo) && "1".equalsIgnoreCase(type) && item.get("类型").toString().equalsIgnoreCase(type) && ("0".equalsIgnoreCase(item.get("换票").toString()) || "0".equalsIgnoreCase(item.get("退票").toString()))){
                    continue;
                }else{
                    initNo = item.get("初始报价单号").toString();
                    type = item.get("类型").toString();
                }

                if(values.length() > 0){
                    values.append(",");
                }
                values.append("('").append(item.get("初始报价单号").toString()).append("','").append(item.get("报价编号").toString()).append("','").append(item.get("处理时间").toString()).append("','").append(item.get("类型").toString()).append("')");

                if((index > 0 && index % 1000 == 0) || index == result.size() - 1){
                    super.executeUpdate(insert + values.toString(), null);
                    values.setLength(0);
                }
            }

            if(values.length() > 0){
                super.executeUpdate(insert + values.toString(), null);
            }
        }
    }
}

